using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Xml.Serialization;

using MyGeneration.dOOdads;

namespace dOOdad_Demo
{
	/// <summary>
	/// Summary description for TheMasterSample.
	/// </summary>
	public class TheMasterSample
	{
		public void SimpleLoad()
		{
			// LoadAll() expects a stored proc to exist in your database
			Employees emps = new Employees();
			if(emps.LoadAll())   // [proc_EmployeesLoadAll]
			{
				// At least one row was loaded
			}

			// LoadAll is the same as (but maybe less efficient) than this
			emps = new Employees();
			if(emps.Query.Load()) // SELECT * FROM Employees
			{
				// At least one row was loaded
			}

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// LoadAll() and Query.Load() with no Where clause yield the same results
			// however, Query.Load() builds dynamic SQL, all query data is passed
			// in via SqlParameters so there is no chance for hackers to attempt
			// "sql injection" techniques
			//-----------------------------------------------------------
		}

		public void MoreComplexLoad()
		{
			Employees emps = new Employees();

			// LastNames that have "A" anywhere in them
			emps.Where.LastName.Value = "%A%";
			emps.Where.LastName.Operator = WhereParameter.Operand.Like;

			emps.Query.Load();

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// You can Query just about anyway you need to with the Where
			// clause, don't bother writing tons of specific stored procedures
			// that you'll later have to maintain
			//-----------------------------------------------------------
		}

		public void TheDeluxeQuery()
		{
			Employees emps = new Employees();

			// LastNames that have "A" anywher in them
			emps.Where.LastName.Value = "%A%";
			emps.Where.LastName.Operator = WhereParameter.Operand.Like;

			// Only return the EmployeeID and LastName
			emps.Query.AddResultColumn(Employees.ColumnNames.EmployeeID);
			emps.Query.AddResultColumn(Employees.ColumnNames.LastName);

			// Order by LastName 
			// (you can add as many order by columns as you like by repeatedly calling this)
			emps.Query.AddOrderBy(Employees.ColumnNames.LastName, WhereParameter.Dir.ASC);

			// Bring back only distinct rows
			emps.Query.Distinct = true;

			// Bring back the top 10 rows
			emps.Query.Top = 10;

			emps.Query.Load();

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// There are many morals here, again, the power of the dynamic
			// query shines through, you'll create screens so quick even you
			// will be surprized.
			// 
			// 1) Never use strings like "FirstName" for anything, use your
			//    business entities ColumnsNames data, such as "Employees.ColumnNames.EmployeeID"
			//    This way if( you drop the column from the table and regenerate you'll 
			//    get a compile error anywhere it was used. Let the compiler do the 
			//    work for you
			//
			//  2) You can reduce the number of columns returned in your result set
			//     though "ResultColumnAdd" but just remember that if( you access
			//     poperties for columns that you didn't return an exception will
			//     be thrown. 
			//
			//  3) Add as many order by columns that you need by repeatedly calling "AddOrderBy"
			//-----------------------------------------------------------
		}

		public void GenerateSql()
		{
			Employees emps = new Employees();
			string query = emps.Query.GenerateSQL();

			// NOTE: It's better to use 'emps.Query.LastQuery'

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// Below is the query text generated by the query in TheDeluxeQuery
			// GenerateSQL was created as a unit testing device and was left in
			// for debugging purposes. After calling this you cannot load the object.
			// If you want to see the sql after the call use 'emps.Query.LastQuery'
			// 
			// "SELECT DISTINCT TOP 10 EmployeeID,LastName FROM [Employees] WHERE [LastName] " 
			// "LIKE @LastName ORDER BY [LastName] ASC"
			//-----------------------------------------------------------
		}

		public void DataReader()
		{
			Employees emps = new Employees();

			// LastNames that have "A" anywhere in them
			emps.Where.LastName.Value = "%A%";
			emps.Where.LastName.Operator = WhereParameter.Operand.Like;

			SqlDataReader reader = emps.Query.ReturnReader() as SqlDataReader;

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// This can be useful for Quick binding, however, you can always
			// bind to emps.DefaultView. 
			//
			// ReturnReader doesn't actually populate your buisness entity
			// it merely returns data in the SqlDataReader
			//-----------------------------------------------------------
		}

		public void Iteration()
		{
			Employees emps = new Employees();
			if(emps.LoadAll())
			{
				string lastName;

				// Iteration walks the DataTable.DefaultView, see the FilterAndSort
				// sample for further clarification.
				do
					lastName = emps.LastName;
				while(emps.MoveNext());

				emps.Rewind();

				do
					lastName = emps.LastName;
				while(emps.MoveNext());
			}

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// Iteration is simple, you can rewind and restart at any time
			//-----------------------------------------------------------
		}

		public void FilterAndSort()
		{
			Employees emps = new Employees();
			if(emps.LoadAll())
			{
				// After you load your business entity you can apply Sort and Filter,
				// you could also potentially do this in the Where clause too
				emps.Filter = Employees.ColumnNames.City + " = 'Berlin'";
				emps.Sort   = Employees.ColumnNames.LastName + " DESC";

				// Filter might have "hidden" all of the rows
				if(emps.RowCount > 0)
				{
					string lastName;

					// Remember, iteration walks the DataTable.DefaultView which is 
					// effected by Sort and Filter, so you'll only get Employees who
					// live in Berlin, and they'll fed to your MoveNext() loop in decending
					// order by LastName
					do
						lastName = emps.LastName;
					while(emps.MoveNext());
				}

				//-----------------------------------------------------------
				// Moral: 
				//-----------------------------------------------------------
				// Sort and Filter can be great ways to implement data grid
				// filtering in your user interface when you don't want to requery 
				// the database, just bind your grid to emps.DefaultView
				//-----------------------------------------------------------
			}
		}

		public void DemonstrateBulkUpdates()
		{
			Employees emps = new Employees();
			if(emps.LoadAll())
			{
				// Modify the LastName column in every row
				do
					emps.LastName += "W";
				while(emps.MoveNext());
			}

			// Rewind and mark the first row as Deleted
			//emps.Rewind();
			//emps.MarkAsDeleted();

			// Add a new row and fill it in
			emps.AddNew();
			emps.FirstName = "Jimmy";
			emps.LastName = "Lunch Box";

			// Save all modifications, deletes, and new rows 
			emps.Save();

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// This is a very nice way to work. When you generate your
			// stored procedures using 'SQL_StoredProcs.vbgen' you'll find
			// it is pretty smart. It makes any identity columns
			// and or computed columns as OUTPUT parameters. Thus, after Save()
			// any new rows or updated rows have their identity
			// columns or calulated columns already in them, no
			// requerying the database
			// 
			// You never have to use a transaction when saving a single object.
			// The dOOdad architecture always does this for you.         
			//-----------------------------------------------------------
		}

		public void Transactions()
		{
			TransactionMgr tx = TransactionMgr.ThreadTransactionMgr();

			try
			{
				Employees emps = new Employees();
				emps.AddNew();
				emps.FirstName = "Jimmy";
				emps.LastName = "Lunch Box";

				Products prds = new Products();
				prds.AddNew();
				prds.ProductName = "dOOdads";
				prds.Discontinued = false;

				tx.BeginTransaction();
				emps.Save();
				prds.Save();
				tx.CommitTransaction();
			}
			catch(Exception)
			{
				tx.RollbackTransaction();
				TransactionMgr.ThreadTransactionMgrReset();
			}

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// Modeled after COM+ transactions, but still using ADO.NET
			// connection based transactions you have the best of both
			// worlds.
			//
			// 1) Your transactions paths do not have to be pre-planned.
			//    At any time you can begin a transaction
			// 
			// 2) You can nest BeginTransaction/CommitTransaction any number of times as
			//    long as they are sandwiched appropriately
			//
			//    BeginTransaction
			//        BeginTransaction
			//            emps.Save
			//        CommitTransaction                                        
			//    CommitTransaction
			//
			//    Only the final CommitTransaction will commit the transaction
			//                        
			// 3) Once RollbackTransaction is called the transaction is doomed,
			//    nothing can be committed even it is attempted.
			//
			// 4) Transactions are stored in the Thread Local Storage or
			//    TLS. This way the API isn't intrusive, ie, forcing you
			//    to pass a SqlConnection around everywhere.  There is one
			//    thing to remember, once you call RollbackTransaction you will
			//    be unable to commit anything on that thread until you
			//    call ThreadTransactionMgrReset().
			// 
			//    In an ASP.NET application each page is handled by a thread
			//    that is pulled from a thread pool. Thus, you need to clear
			//    out the TLS (thread local storage) before your page begins
			//    execution. The best way to do this is to create a base page
			//    that inhertis from System.Web.UI.Page and clears the state
			//    like this
			//
			//    public class MyPage : System.Web.UI.Page
			//	  {
			//        private void Page_Init(System.Object sender, System.EventArgs e)
			//		  {
			//           TransactionMgr.ThreadTransactionMgrReset();
			//        }
			//    }
			//
			//    And then make sure all of your ASPX pages inherit from MyPage.
			//                        
			//-----------------------------------------------------------
		}

		public void FillComboBox()
		{
			Products prds = new Products();

			// Note we only bring back these two columns for performance reasons, why bring back more?
			prds.Query.AddResultColumn(Products.ColumnNames.ProductID);
			prds.Query.AddResultColumn(Products.ColumnNames.ProductName);

			// Sort
			prds.Query.AddOrderBy(Products.ColumnNames.ProductName, MyGeneration.dOOdads.WhereParameter.Dir.ASC);

			// Load it
			prds.Query.Load();

			// Bind it (there no combo box in this code, see demo)
			// Me.cmbBox.DisplayMember = prds.ColumnNames.ProductName
			// Me.cmbBox.DataSource    = prds.DefaultView

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// You will find that a dOOdads can do almost anything, no need to write a million little 
			// specific stored procedures, this code limits the columns, sorts, and fills a combobox
			// there's nothing to it
			//-----------------------------------------------------------
		}

		public void AddColumn()
		{
			Employees emps = new Employees();
			if(emps.LoadAll())
			{
				DataColumn col = emps.AddColumn("FullName", Type.GetType("System.String"));
				col.Expression = Employees.ColumnNames.LastName + "+ ', ' + " + Employees.ColumnNames.FirstName;

				string fullName;

				do
					fullName = emps.GetColumn("FullName") as string;
				while(emps.MoveNext());
			}

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// Of course if( you add a column Dynamically as the code does above
			// you'll have no strongly typed accessor like emps.FullName, but you
			// can use GetColumn() to access dynamic columns.
			//
			// Never use this to access other fields in your business entity
			// although it may work, it's poor programming and always use
			// your ColumnNames property and not hardcoded strings when possible
			//-----------------------------------------------------------
		}

		public void Serialize()
		{
			Employees emps = new Employees();
			emps.LoadAll();                // emps.RowCount = 200
			emps.LastName = "Griffinski";  // Change first row
			emps.GetChanges();             // emps.RowCount now = 1 
			string str = emps.ToXml();
            
			// Now reload that single record into a new Employees object and Save it
			Employees empsClone = new Employees();
			empsClone.FromXml(str);
			empsClone.Save();

			//-----------------------------------------------------------
			// Moral: 
			//-----------------------------------------------------------
			// This really only serializes the data in the embedded DataTable.
			// However, the methods used in the sample above our virtual
			// so you can override them.
			//-----------------------------------------------------------
		}

		public void DataSetSerialize()
		{
			Employees emps = new Employees();
			emps.LoadAll();                // emps.RowCount = 200
			emps.LastName = "Griffinski";  // Change first row
			emps.GetChanges();             // emps.RowCount now = 1 
			string str = emps.Serialize(); 
            
			// Now reload that single record into a new Employees object and Save it
			Employees empsClone = new Employees();
			empsClone.Deserialize(str);
			empsClone.Save();
		}

		public void AggregateTest()
		{
			// AVG
			Products prds = new Products();
			prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Avg;
			prds.Aggregate.UnitsInStock.Alias = "Average Units in Stock";
			prds.Query.Load();

			Console.WriteLine(prds.Query.LastQuery);

			// COUNT - To include a COUNT(*) with NULLs included
			prds = new Products();
			prds.Query.CountAll = true;
			prds.Query.CountAllAlias = "Product Count";
			prds.Query.Load();

			Console.WriteLine(prds.Query.LastQuery);

			// COUNT - To exclude NULLs in the COUNT for a column
			prds = new Products();
			prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Count;
			prds.Aggregate.UnitsInStock.Alias = "With Stock";
			prds.Query.Load();

			Console.WriteLine(prds.Query.LastQuery);

			// To have two aggregates for the same column, use a  Tearoff
			prds = new Products();
			prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Count;
			prds.Aggregate.UnitsInStock.Alias = "With Stock";

			AggregateParameter ap = prds.Aggregate.TearOff.UnitsInStock;
			ap.Function = AggregateParameter.Func.Sum;
			ap.Alias = "Total Units";

			prds.Query.Load();

			Console.WriteLine(prds.Query.LastQuery);

			// AddGroupBy
			prds = new Products();
			prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Count;
			prds.Aggregate.UnitsInStock.Alias = "In Stock";
			prds.Query.AddResultColumn(Products.ColumnNames.CategoryID);
			prds.Query.AddGroupBy(Products.ColumnNames.CategoryID);
			//			prds.Query.AddGroupBy(prds.Aggregate.UnitsInStock);

			prds.Query.Load();

			Console.WriteLine(prds.Query.LastQuery);
		}
	}
}
